package com.wntms.ft.dao;

import com.wntms.ft.pojo.FareForm;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface FareDao {

    /**
     * 分类查询所有货物合同信息
     * @return
     */
    @Select("SELECT uid,SUM(fare) AS total,SUM(insurancefare) AS ins_fare,SUM(payment) AS payment\n" +
            "\tFROM freight_contract\n" +
            "\tGROUP BY uid\n" +
            "\tORDER BY total desc")
    @Results({
            @Result(column = "uid",property = "id"),
            @Result(column = "total",property = "fare"),
            @Result(column = "ins_fare",property = "premium"),
            @Result(column = "payment",property = "prepaidAmount")
    })
   public List<FareForm> findAllFC();

    /**
     *根据用户id查询用户姓名
     * @param id
     * @return
     */
    @Select("select name from customer_information where id=#{id}")
    public String findByCustomer(Integer id);


    /**
     * 根据用户id查询
     * @param id
     * @return
     */

    //需要修改
    @Select("SELECT SUM(receivables) AS re,SUM(after_receivables) AS ar,SUM(commission) AS com\n" +
            "\tFROM collection_settle\n" +
            "\tWHERE cid = #{id}\n" +
            "\tGROUP BY cid")
    @Results({
            @Result(column = "re",property = "prepaidAmount"),
            @Result(column = "ar",property = "changeFare"),
    })
    public FareForm findById(Integer id);

    /**
     * 根据时间查询货物合同表
     * @return
     */
    @Select("SELECT uid,SUM(fare) AS total,SUM(insurancefare) AS ins_fare,SUM(payment) AS payment\n" +
            "\tFROM freight_contract\n" +
            "\twhere sdate > #{date}\n" +
            "\tGROUP BY uid\n" +
            "\tORDER BY total desc")
    @Results({
            @Result(column = "uid",property = "id"),
            @Result(column = "total",property = "fare"),
            @Result(column = "ins_fare",property = "premium"),
            @Result(column = "payment",property = "prepaidAmount")
    })
   public List<FareForm> findByData(String date);





    /**
     * 表格数据展示
     * @return
     */
    @Select("SELECT uid,SUM(fare) AS total,SUM(insurancefare) AS ins_fare,SUM(payment) AS payment\n" +
            "\tFROM freight_contract\n" +
            "\tGROUP BY uid\n" +
            "\tORDER BY total desc" +
            "\tlimit 0,5"
            )
    @Results({
            @Result(column = "uid",property = "id"),
            @Result(column = "total",property = "fare"),
            @Result(column = "ins_fare",property = "premium"),
            @Result(column = "payment",property = "prepaidAmount")
    })
    public List<FareForm> findByLimit();



    /**
     * 表格展示：分页分条件查询
     * @param date
     * @return
     */

    @Select("SELECT uid,SUM(fare) AS total,SUM(insurancefare) AS ins_fare,SUM(payment) AS payment\n" +
            "\tFROM freight_contract\n" +
            "\twhere sdate > #{date}\n" +
            "\tGROUP BY uid\n" +
            "\tORDER BY total desc" +
            "\tlimit 0,5"
             )
    @Results({
            @Result(column = "uid",property = "id"),
            @Result(column = "total",property = "fare"),
            @Result(column = "ins_fare",property = "premium"),
            @Result(column = "payment",property = "prepaidAmount")
    })
    public List<FareForm> findByLimitAndDate(String date);



}



